const { Sequelize } = require('sequelize');

// 数据库配置
const sequelize = new Sequelize('lingxi_system', 'root', '888mei525', {
  host: 'localhost',
  dialect: 'mysql',
  timezone: '+08:00',
  logging: false
});

async function seedBankTransactions() {
  try {
    // 测试数据库连接
    await sequelize.authenticate();
    console.log('数据库连接成功');
    
    // 创建与销售订单匹配的银行流水数据
    const transactions = [
      {
        transaction_id: 'TXN202511020021',
        payer_name: '客户D',
        payer_account: '6222021234567890001',
        amount: 25866.00,
        transaction_date: '2025-11-02',
        transaction_time: '2025-11-02 10:30:00',
        balance: 125866.00,
        remark: '销售订单SO202511020021付款',
        data_source: 'file_import',
        import_batch: 'batch_20251113_manual',
        is_filtered: false,
        created_at: new Date()
      },
      {
        transaction_id: 'TXN202510270036',
        payer_name: '客户H',
        payer_account: '6222021234567890002',
        amount: 67891.00,
        transaction_date: '2025-10-27',
        transaction_time: '2025-10-27 14:15:00',
        balance: 97891.00,
        remark: '销售订单SO202510270036付款',
        data_source: 'file_import',
        import_batch: 'batch_20251113_manual',
        is_filtered: false,
        created_at: new Date()
      },
      {
        transaction_id: 'TXN202510180038',
        payer_name: '客户H',
        payer_account: '6222021234567890003',
        amount: 78246.00,
        transaction_date: '2025-10-18',
        transaction_time: '2025-10-18 09:45:00',
        balance: 88246.00,
        remark: '销售订单SO202510180038付款',
        data_source: 'file_import',
        import_batch: 'batch_20251113_manual',
        is_filtered: false,
        created_at: new Date()
      },
      {
        transaction_id: 'TXN202510170039',
        payer_name: '客户I',
        payer_account: '6222021234567890004',
        amount: 88094.00,
        transaction_date: '2025-10-17',
        transaction_time: '2025-10-17 16:20:00',
        balance: 98094.00,
        remark: '销售订单SO202510170039付款',
        data_source: 'file_import',
        import_batch: 'batch_20251113_manual',
        is_filtered: false,
        created_at: new Date()
      },
      {
        transaction_id: 'TXN202301310047',
        payer_name: '客户B',
        payer_account: '6222021234567890005',
        amount: 75000.00,
        transaction_date: '2023-01-31',
        transaction_time: '2023-01-31 11:30:00',
        balance: 85000.00,
        remark: '销售订单SO202302010047付款',
        data_source: 'file_import',
        import_batch: 'batch_20251113_manual',
        is_filtered: false,
        created_at: new Date()
      },
      {
        transaction_id: 'TXN202301240046',
        payer_name: '客户A',
        payer_account: '6222021234567890006',
        amount: 25000.00,
        transaction_date: '2023-01-24',
        transaction_time: '2023-01-24 13:45:00',
        balance: 35000.00,
        remark: '销售订单SO202301250046付款',
        data_source: 'file_import',
        import_batch: 'batch_20251113_manual',
        is_filtered: false,
        created_at: new Date()
      },
      {
        transaction_id: 'TXN202301190045',
        payer_name: '客户E',
        payer_account: '6222021234567890007',
        amount: 65000.00,
        transaction_date: '2023-01-19',
        transaction_time: '2023-01-19 15:20:00',
        balance: 75000.00,
        remark: '销售订单SO202301200045付款',
        data_source: 'file_import',
        import_batch: 'batch_20251113_manual',
        is_filtered: false,
        created_at: new Date()
      },
      {
        transaction_id: 'TXN202301190052',
        payer_name: '客户E',
        payer_account: '6222021234567890008',
        amount: 68000.00,
        transaction_date: '2023-01-19',
        transaction_time: '2023-01-19 16:10:00',
        balance: 78000.00,
        remark: '销售订单SO202301200052付款',
        data_source: 'file_import',
        import_batch: 'batch_20251113_manual',
        is_filtered: false,
        created_at: new Date()
      },
      {
        transaction_id: 'TXN202301140044',
        payer_name: '客户D',
        payer_account: '6222021234567890009',
        amount: 45000.00,
        transaction_date: '2023-01-14',
        transaction_time: '2023-01-14 10:15:00',
        balance: 55000.00,
        remark: '销售订单SO202301150044付款',
        data_source: 'file_import',
        import_batch: 'batch_20251113_manual',
        is_filtered: false,
        created_at: new Date()
      },
      {
        transaction_id: 'TXN202301140051',
        payer_name: '客户D',
        payer_account: '6222021234567890010',
        amount: 48000.00,
        transaction_date: '2023-01-14',
        transaction_time: '2023-01-14 11:30:00',
        balance: 58000.00,
        remark: '销售订单SO202301150051付款',
        data_source: 'file_import',
        import_batch: 'batch_20251113_manual',
        is_filtered: false,
        created_at: new Date()
      }
    ];
    
    // 插入数据到数据库
    for (const transaction of transactions) {
      try {
        const query = `
          INSERT INTO bank_transactions (
            transaction_id, payer_name, payer_account, amount, transaction_date,
            transaction_time, balance, remark, data_source, import_batch,
            is_filtered, created_at
          ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        `;
        
        const values = [
          transaction.transaction_id, transaction.payer_name, transaction.payer_account, transaction.amount, transaction.transaction_date,
          transaction.transaction_time, transaction.balance, transaction.remark, transaction.data_source, transaction.import_batch,
          transaction.is_filtered, transaction.created_at
        ];
        
        await sequelize.query(query, {
          replacements: values,
          type: Sequelize.QueryTypes.INSERT
        });
        
        console.log(`成功插入银行流水: ${transaction.transaction_id}`);
      } catch (insertError) {
        console.error(`插入银行流水 ${transaction.transaction_id} 时出错:`, insertError.message);
      }
    }
    
    console.log(`成功插入 ${transactions.length} 条银行流水数据`);
    
    // 验证数据插入
    const [countResult] = await sequelize.query('SELECT COUNT(*) as count FROM bank_transactions');
    console.log(`银行流水表总记录数: ${countResult[0].count}`);
    
    // 关闭连接
    await sequelize.close();
  } catch (error) {
    console.error('数据填充时出错:', error.message);
    console.error('错误堆栈:', error.stack);
  }
}

seedBankTransactions();